/* @(#)db.c
 * Time-stamp: <2008-10-25 18:42:36 root>
 * Copyright 2008 Julian Qian
 * Author: root@localhost.localdomain
 * Version: $Id: db.c,v 0.0 2008/09/27 13:42:14 root Exp $
 * Keywords: 
 */

#include "common.h"

int db_init(DB_QUERY *dbq, DB_CONN *dbc){
	memset(dbq, 0, sizeof(DB_QUERY)); /* clear memory */
	
	dbq->conn = mysql_init(NULL);

	if(mysql_real_connect(dbq->conn,dbc->hostname,dbc->username,dbc->password,dbc->database,0,NULL,0) == NULL) {
		err_msg("mysql_real_connect");
		return -1;
	}
	dbq->mutex = mutex_create(TRUE);
	return 0;
}

int db_stmt_prepare(DB_QUERY *dbq){
	int param_count;

	MYSQL_BIND insert_bind[4];
	memset(insert_bind, 0, sizeof(insert_bind));

	insert_bind[0].buffer_type= MYSQL_TYPE_LONG;
	insert_bind[0].buffer= &(dbq->insert_ip);
	insert_bind[0].is_null= 0;
	insert_bind[0].length= 0;
	insert_bind[0].is_unsigned = TRUE;

	insert_bind[1].buffer_type= MYSQL_TYPE_LONG;
	insert_bind[1].buffer= &(dbq->insert_spec);
	insert_bind[1].is_null= (my_bool*)0;
	insert_bind[1].length= 0;

	insert_bind[2].buffer_type= MYSQL_TYPE_LONG;
	insert_bind[2].buffer= &(dbq->insert_stat);
	insert_bind[2].is_null= (my_bool*)0;
	insert_bind[2].length= 0;

	insert_bind[3].buffer_type= MYSQL_TYPE_LONG;
	insert_bind[3].buffer= &(dbq->insert_devid);
	insert_bind[3].is_null= 0;
	insert_bind[3].length= 0;

	MYSQL_BIND update_bind[3];
	memset(update_bind, 0, sizeof(update_bind));

	update_bind[0].buffer_type= MYSQL_TYPE_LONG;
	update_bind[0].buffer= &(dbq->update_ip);
	update_bind[0].is_null= 0;
	update_bind[0].length= 0;
	update_bind[0].is_unsigned = TRUE; /* IMPORTANT */

	update_bind[1].buffer_type= MYSQL_TYPE_LONG;
	update_bind[1].buffer= &(dbq->update_stat);
	update_bind[1].is_null= 0;
	update_bind[1].length= 0;

	update_bind[2].buffer_type= MYSQL_TYPE_LONG;
	update_bind[2].buffer= &(dbq->update_devid);
	update_bind[2].is_null= 0;
	update_bind[2].length= 0;
	
	MYSQL_BIND select_bind[1];
	memset(select_bind, 0, sizeof(select_bind));

	select_bind[0].buffer_type = MYSQL_TYPE_LONG;
	select_bind[0].buffer = &(dbq->select_devid);
	select_bind[0].is_null = 0;
	select_bind[0].length = 0;

	MYSQL_BIND sel_devid_bind[1];
	memset(sel_devid_bind, 0, sizeof(sel_devid_bind));
	
	sel_devid_bind[0].buffer_type = MYSQL_TYPE_STRING;
	sel_devid_bind[0].buffer = &(dbq->sel_devid_phone);
	sel_devid_bind[0].buffer_length = PHONE_SIZE;
	sel_devid_bind[0].is_null = 0;
	sel_devid_bind[0].length = &(dbq->sel_devid_phone_len);

	MYSQL_BIND sel_stat_bind[1];
	memset(sel_stat_bind, 0, sizeof(sel_stat_bind));

	sel_stat_bind[0].buffer_type = MYSQL_TYPE_LONG;
	sel_stat_bind[0].buffer = &(dbq->sel_stat_devid);
	sel_stat_bind[0].is_null = 0;
	sel_stat_bind[0].length = 0;

	MYSQL_BIND sel_ip_bind[1];
	memset(sel_ip_bind, 0, sizeof(sel_ip_bind));

	sel_ip_bind[0].buffer_type = MYSQL_TYPE_LONG;
	sel_ip_bind[0].buffer = &(dbq->sel_ip_devid);
	sel_ip_bind[0].is_null = 0;
	sel_ip_bind[0].length = 0;

	MYSQL_BIND update_stat_bind[2];
	memset(update_stat_bind, 0, sizeof(update_stat_bind));

	update_stat_bind[0].buffer_type = MYSQL_TYPE_LONG;
	update_stat_bind[0].buffer = &(dbq->update_stat_stat);
	update_stat_bind[0].is_null = 0;
	update_stat_bind[0].length = 0;

	update_stat_bind[1].buffer_type = MYSQL_TYPE_LONG;
	update_stat_bind[1].buffer = &(dbq->update_stat_devid);
	update_stat_bind[1].is_null = 0;
	update_stat_bind[1].length = 0;

	MYSQL_BIND auth_bind[1];
	memset(auth_bind, 0, sizeof(auth_bind));

	auth_bind[0].buffer_type = MYSQL_TYPE_LONG;
	auth_bind[0].buffer = &(dbq->auth_devid);
	auth_bind[0].is_null = 0;
	auth_bind[0].length = 0;
	
	char insert_sql[] = "insert into status (ip, type, stat, devid, up_time) values (?,?,?,?,UNIX_TIMESTAMP())";
	char update_sql[] = "update status set ip=?,stat=?,up_time=UNIX_TIMESTAMP() where devid=?";
	char select_sql[] = "select mobile from user where devid=? limit 1";
	char sel_devid_sql[] = "select devid from user where mobile=? limit 1";
	char sel_stat_sql[] = "select stat from status where devid=? order by up_time desc limit 1";
	char sel_ip_sql[] = "select ip from status where devid=? limit 1";
	char update_stat_sql[] = "update status set stat=? where devid=?";
	char auth_sql[] = "select uid, locked from user where devid=? limit 1";

	//stmt insert
	if(!(dbq->insert_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->insert_stmt, insert_sql, strlen(insert_sql))) {
		err_msg("mysql_stmt_prepare");
	}
	
	/* Get the parameter count from the statement */
	param_count= mysql_stmt_param_count(dbq->insert_stmt);
	err_msg(" total parameters in INSERT: %d", param_count);

	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->insert_stmt, insert_bind)){
		err_msg("mysql_stmt_bind_param");
	}

	/* ################################################################ */
	//stmt update
	if(!(dbq->update_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->update_stmt, update_sql, strlen(update_sql))) {
		err_msg("mysql_stmt_prepare");
	}
	
	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->update_stmt, update_bind)){
		err_msg("mysql_stmt_bind_param");
	}

	/* ################################################################ */
	//stmt select
	if(!(dbq->select_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->select_stmt, select_sql, strlen(select_sql))) {
		err_msg("mysql_stmt_prepare");
	}

	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->select_stmt, select_bind)){
		err_msg("mysql_stmt_bind_param");
	}
	
	/* ################################################################ */
	//stmt 
	if(!(dbq->sel_devid_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->sel_devid_stmt, sel_devid_sql, strlen(sel_devid_sql))) {
		err_msg("mysql_stmt_prepare");
	}
	
	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->sel_devid_stmt, sel_devid_bind)){
		err_msg("mysql_stmt_bind_param");
	}
	
	/* ################################################################ */
	//stmt 
	if(!(dbq->sel_stat_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->sel_stat_stmt, sel_stat_sql, strlen(sel_stat_sql))) {
		err_msg("mysql_stmt_prepare");
	}
	
	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->sel_stat_stmt, sel_stat_bind)){
		err_msg("mysql_stmt_bind_param");
	}

	/* ################################################################ */
	//stmt 
	if(!(dbq->sel_ip_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->sel_ip_stmt, sel_ip_sql, strlen(sel_ip_sql))) {
		err_msg("mysql_stmt_prepare");
	}
	
	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->sel_ip_stmt, sel_ip_bind)){
		err_msg("mysql_stmt_bind_param");
	}
	
	/* ################################################################ */
	//stmt 
	if(!(dbq->update_stat_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->update_stat_stmt, update_stat_sql, strlen(update_stat_sql))) {
		err_msg("mysql_stmt_prepare");
	}
	
	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->update_stat_stmt, update_stat_bind)){
		err_msg("mysql_stmt_bind_param");
	}
	
	/* ################################################################ */
	//stmt 
	if(!(dbq->auth_stmt = mysql_stmt_init(dbq->conn))){
		err_msg("mysql_stmt_init");
	}
	
	if (mysql_stmt_prepare(dbq->auth_stmt, auth_sql, strlen(auth_sql))) {
		err_msg("mysql_stmt_prepare");
	}
	
	/* Bind the buffers */
	if (mysql_stmt_bind_param(dbq->auth_stmt, auth_bind)){
		err_msg("mysql_stmt_bind_param");
	}

	return 0;
}

int db_stmt_query(MYSQL_STMT *stmt){
	int ret = 0, i = 0;
	while( mysql_stmt_execute(stmt) ) {
		/* err_sys("mysql_stmt_execute failed."); */
		err_sys("mysql_stmt_execute failed: %s", mysql_stmt_errno(stmt));
		if(i++ > 5) break;
	}
	/* ret = mysql_stmt_affected_rows(stmt); */
	/* printf("-->%d<-- rows affected.", ret); */
	return ret;
}

int db_stmt_affected(MYSQL_STMT *stmt){
	return mysql_stmt_affected_rows(stmt);
}

/* int db_stmt_phone(MYSQL_STMT *stmt, devid_t devid, char *phone){ */
int db_stmt_phone(DB_QUERY *dbq, char *phone){
	MYSQL_BIND bind[1];
	my_bool is_null[1];
	unsigned long length[1];
	my_bool error[1];

	memset(bind, 0, sizeof(bind));
	
	bind[0].buffer_type = MYSQL_TYPE_STRING;
	bind[0].buffer = (char *)phone;
	bind[0].buffer_length = PHONE_SIZE;
	bind[0].is_null = &is_null[0];
	bind[0].length = &length[0];
	bind[0].error = &error[0];

	/* bind the result buffers */
	if(mysql_stmt_bind_result(dbq->select_stmt, bind)){
		/* error */
		return -1;
	}

	if(! mysql_stmt_fetch(dbq->select_stmt)){
		mysql_stmt_free_result(dbq->select_stmt);		
		err_msg("Get mobile: %s", phone);
	}else{
		/* error */
		err_msg("Non mobile found!");
	}
	return 0;
}

devid_t db_stmt_sel_devid(DB_QUERY *dbq){
	MYSQL_BIND bind[1];
	my_bool is_null[1];
	unsigned long length[1];
	my_bool error[1];

	devid_t devid;
	
	memset(bind, 0, sizeof(bind));
	
	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (devid_t *)&devid;
	bind[0].is_null = &is_null[0];
	bind[0].length = &length[0];
	bind[0].error = &error[0];

	/* bind the result buffers */
	if(mysql_stmt_bind_result(dbq->sel_devid_stmt, bind)){
		/* error */
		return -1;
	}

	if(! mysql_stmt_fetch(dbq->sel_devid_stmt)){
		err_msg("Get device ID: %ld", devid);
		mysql_stmt_free_result(dbq->sel_devid_stmt);
		return devid;
	}
	return -1;
}

stat_t db_stmt_sel_stat(DB_QUERY *dbq){
	MYSQL_BIND bind[1];
	my_bool is_null[1];
	unsigned long length[1];
	my_bool error[1];

	stat_t stat;
	
	memset(bind, 0, sizeof(bind));
	
	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (stat_t *)&stat;
	bind[0].is_null = &is_null[0];
	bind[0].length = &length[0];
	bind[0].error = &error[0];

	/* bind the result buffers */
	if(mysql_stmt_bind_result(dbq->sel_stat_stmt, bind)){
		/* error */
		return -1;
	}

	if(! mysql_stmt_fetch(dbq->sel_stat_stmt)){
		mysql_stmt_free_result(dbq->sel_stat_stmt);				
		err_msg("Get status: %d", stat);
		return stat;
	}
	return STAT_OTHER;
}

ip_t db_stmt_sel_ip(DB_QUERY *dbq){
	MYSQL_BIND bind[1];
	my_bool is_null[1];
	unsigned long length[1];
	my_bool error[1];

	ip_t ip;
	
	memset(bind, 0, sizeof(bind));
	
	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (ip_t *)&ip;
	bind[0].is_null = &is_null[0];
	bind[0].length = &length[0];
	bind[0].error = &error[0];
	bind[0].is_unsigned = TRUE;

	/* bind the result buffers */
	if(mysql_stmt_bind_result(dbq->sel_ip_stmt, bind)){
		/* error */
		return -1;
	}

	if(! mysql_stmt_fetch(dbq->sel_ip_stmt)){
		mysql_stmt_free_result(dbq->sel_ip_stmt);
		err_msg("Get IP: %d", ip);
		return ip;
	}
	return 0;
}

bool db_stmt_auth(DB_QUERY *dbq){
	MYSQL_BIND bind[2];
	my_bool is_null[2];
	unsigned long length[2];
	my_bool error[2];

	uid_t uid;
	locked_t locked;
	
	memset(bind, 0, sizeof(bind));
	
	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (uid_t *)&uid;
	bind[0].is_null = &is_null[0];
	bind[0].length = &length[0];
	bind[0].error = &error[0];

	bind[1].buffer_type = MYSQL_TYPE_LONG;
	bind[1].buffer = (locked_t *)&locked;
	bind[1].is_null = &is_null[1];
	bind[1].length = &length[1];
	bind[1].error = &error[1];
	
	/* bind the result buffers */
	if(mysql_stmt_bind_result(dbq->auth_stmt, bind)){
		/* error */
		return -1;
	}

	if(! mysql_stmt_fetch(dbq->auth_stmt)){
		mysql_stmt_free_result(dbq->auth_stmt);				
		err_msg("auth uid: %d, locked: %d", uid, locked);
		if(locked == 0){
			return TRUE;
		}
	}
	return FALSE;
}


int db_query_phone_obsolote(DB_CONN *dbc, devid_t devid, char *phone){
	MYSQL *conn;
	MYSQL_RES *res;
	MYSQL_ROW row;
	conn = mysql_init(NULL);
	if(mysql_real_connect(conn,dbc->hostname,dbc->username,dbc->password,dbc->database,0,NULL,0) == NULL) {
		err_msg("mysql_real_connect");
	}
	char sql[50];
	sprintf(sql, "select mobile from user where devid=%ld", devid);
	mysql_real_query(conn, sql, strlen(sql));

	if(!mysql_affected_rows(conn)){
		return -1;
	}
	res = mysql_store_result(conn);
	row = mysql_fetch_row(res);
	err_msg("mobile: %s", row[0]);
	strncpy(phone, row[0], PHONE_SIZE);

	mysql_close(conn);	
	return 0;
}

int db_stmt_release(DB_QUERY *dbq){
	if( dbq->insert_stmt ) mysql_stmt_close(dbq->insert_stmt);
	if( dbq->update_stmt ) mysql_stmt_close(dbq->update_stmt);
	if( dbq->select_stmt ) mysql_stmt_close(dbq->select_stmt);
	if( dbq->sel_devid_stmt ) mysql_stmt_close(dbq->sel_devid_stmt);
	if( dbq->sel_stat_stmt ) mysql_stmt_close(dbq->sel_stat_stmt);
	if( dbq->sel_ip_stmt ) mysql_stmt_close(dbq->sel_ip_stmt);
	if( dbq->update_stat_stmt ) mysql_stmt_close(dbq->update_stat_stmt);
	if( dbq->auth_stmt ) mysql_stmt_close(dbq->auth_stmt);		
	if( dbq->conn ) mysql_close(dbq->conn);

	mutex_destroy(dbq->mutex);
}

int db_insert_status(DB_CONN *dbc, char *devid, char *ip, int *stat){
}
